# -*- coding: utf-8 -*-
# @Time    : 2019/10/25 13:53
# @Author  : zhouqiang
# @Site    : 
# @File    : fund_nav_related.py
# @Software: PyCharm

"""
脚本说明: 获取基金、基金经理净值相关数据
"""
from quant_researcher.quant.project_tool.db_operator import db_conn, my_mysql
from quant_researcher.quant.project_tool import time_tool
from quant_researcher.quant.project_tool.wrapper_tools import common_wrappers, conn_wrappers
from quant_researcher.quant.project_tool.celebrity import get_lst_condition
from quant_researcher.quant.project_tool.logger.my_logger import LOG
from quant_researcher.quant.datasource_fetch.common_data_api.common_tools import get_max_date_of_table

T_FUND_ADJ_NAV = 'mf_di_fndadjnav'
T_FUND_ADJ_INTERP_NAV = 'mf_di_fndadjnav_interp'
T_FUND_DAILY_RETURN = 'mf_di_fnddailyreturn'
T_FUND_MONTHLY_RETURN = 'mf_di_fndmonthlyreturn'
T_FUND_ORIGIN_NAV = 'mf_nv_netvalue'
T_FUND_DIVIDEND = 'mf_ri_dividsplit'
T_FUND_RATING = 'mf_di_fndstar'
T_MANAGER_NAV = 'mf_di_mgradjnav'
T_MANAGER_DAILY_RETURN = 'mf_di_mgrdailyreturn'
T_MANAGER_MONTHLY_RETURN = 'mf_di_mgrmonthlyreturn'
T_COMPANY_NAV = 'mf_di_companyadjnav'
T_COMPANY_DAILY_RETURN = 'mf_di_compdailyreturn'
T_COMPANY_MONTHLY_RETURN = 'mf_di_compmonthlyreturn'
EARLIEST_START_DATE = '1900-01-01'


def get_fund_nav(fund_code=None, start_date=None, end_date=None,
                 only_latest=False, nav_type='adj', freq='D', **kwargs):
    """
    获得基金的单位净值或者复权净值

    :param str or list or None fund_code: 基金代码，支持单个（str）、多个（list）、所有（None）
    :param str or int or datetime.date or datetime.datetime or int start_date: 开始时间，支持带杠字符串、不带杠字符串、datetime、date、int格式
    :param str or int or datetime.date or datetime.datetime or int end_date: 结束时间，支持带杠字符串、不带杠字符串、datetime、date、int格式
    :param bool only_latest: True-只获取最新的一天数据，False-获取一个区间段的数据
    :param str nav_type: adj-复权净值，返回列名adjusted_nav；origin-单位净值，返回列名nav；adj_interp-复权净值插值（针对一周只公布一次净值的基金）
    :param str freq: D-日频；M-月频
    :param kwargs:
        - conn: 数据库连接
    :return: pd.DataFrame
                +-----------+------------+--------------+
                | fund_code |  end_date  | adjusted_nav |
                +-----------+------------+--------------+
                |   110022  | 2020-04-24 |    2.984     |
                |   110022  | 2020-04-27 |    2.963     |
                |   110022  | 2020-04-28 |    3.028     |
                |   110022  | 2020-04-29 |    2.975     |
                |   110022  | 2020-04-30 |    2.997     |
                +-----------+------------+--------------+
    """
    conn = kwargs.pop('conn', None)
    if freq == 'M':
        table = T_FUND_MONTHLY_RETURN
        date_col = '`month`'
        nav_col = 'nav_to as adjusted_nav'
    else:
        if nav_type == 'adj':
            table = T_FUND_ADJ_NAV
            date_col = 'nav_date'
            nav_col = 'adjusted_nav'
        elif nav_type == 'origin':
            table = T_FUND_ORIGIN_NAV
            date_col = 'end_date'
            nav_col = 'nav'
        elif nav_type == 'adj_interp':
            table = T_FUND_ADJ_INTERP_NAV
            date_col = 'nav_date'
            nav_col = 'adjusted_nav_interp'
        else:
            raise NotImplementedError('目前nav_type只支持adj、origin和adj_interp，请重新输入')

    select = ['fund_code', f'{date_col} as end_date', nav_col]

    where = []

    # fund_code过滤条件
    if isinstance(fund_code, str) or isinstance(fund_code, list):
        if isinstance(fund_code, str):
            fund_code = [fund_code]
        fund_where_condition = get_lst_condition(fund_code, 'fund_code')
        where.append(fund_where_condition)
    elif fund_code is None:
        fund_where_condition = '1 = 1'
    else:
        LOG.error('目前fund_code只支持str、list或者None，请按要求输入')
        raise NotImplementedError

    # 将日期转换成带杠字符串
    if start_date is None:
        start_date = EARLIEST_START_DATE
    else:
        start_date = time_tool.format_date_str(start_date)
    if end_date is None:
        end_date = time_tool.get_today(marker='with_n_dash')
    else:
        end_date = time_tool.format_date_str(end_date)

    if freq == 'M':
        start_date = start_date[:7]
        end_date = end_date[:7]

    tmp_conn = db_conn.get_derivative_data_conn() if conn is None else conn
    # 只返回最新数据
    if only_latest:
        select = ', '.join(select)
        sql_str = (f"select {select} from {table} as a, "
                   f"(select fund_code as code, max({date_col}) as enddate "
                   f"from {table} "
                   f"where {date_col} <= '{end_date}' "
                   f"and {fund_where_condition} "
                   f"group by fund_code) as b "
                   f"where a.fund_code = b.code "
                   f"and a.{date_col} = b.enddate ")
        df = my_mysql.read_v3(sql_str, tmp_conn)
        # 返回一段时间数据的where语句
    else:
        where.append(f"{date_col} >= '{start_date}'")
        where.append(f"{date_col} <= '{end_date}'")
        df = my_mysql.read_v2(select=select, where=where, sfrom=table, conn=tmp_conn)

    if conn is None:
        tmp_conn.close()

    if df.empty:
        LOG.error(f"没有找到{fund_code}基金的数据，请检查")
        return

    if freq == 'M':
        df['end_date'] = df['end_date'].apply(time_tool.get_the_end_of_this_month)
    else:
        df['end_date'] = df['end_date'].apply(time_tool.format_date_str)

    return df


def get_fund_return(fund_code=None, start_date=None, end_date=None, only_latest=False, freq='D', **kwargs):
    """
    获得基金的日收益率或者月收益率

    :param str or list or None fund_code: 基金代码，支持单个（str）、多个（list）、所有（None）
    :param str or datetime.date or datetime.datetime or int start_date: 开始时间，
                支持带杠字符串、不带杠字符串、datetime、date、int格式
                若freq=M，只会读取日期的年份和月份
    :param str or datetime.date or datetime.datetime or int end_date: 结束时间，
                支持带杠字符串、不带杠字符串、datetime、date、int格式
                若freq=M，只会读取日期的年份和月份
    :param bool only_latest: True-只获取最新的一期数据，False-获取一个区间段的数据
    :param str freq: D-日收益率，返回列名daily_return；M-月收益率，返回列名monthly_return
    :param kwargs
            conn: 数据库连接
    :return: pd.DataFrame
                +-----------+------------+----------------+
                | fund_code |  end_date  | monthly_return |
                +-----------+------------+----------------+
                |   001625  | 2020-11-30 |     0.0022     |
                |   003095  | 2020-11-30 |    -0.0812     |
                |   110022  | 2020-11-30 |     0.0611     |
                +-----------+------------+----------------+
    """
    conn = kwargs.pop('conn', None)
    tmp_conn = db_conn.get_derivative_data_conn() if conn is None else conn

    if freq == 'D':
        table = T_FUND_DAILY_RETURN
        date_col = '`day`'
        ret_col = 'daily_return'
    elif freq == 'M':
        table = T_FUND_MONTHLY_RETURN
        date_col = '`month`'
        ret_col = 'monthly_return'
    else:
        raise NotImplementedError('目前freq只支持D和M')

    select = ['fund_code', f'{date_col} as end_date', ret_col]

    where = []

    # fund_code过滤条件
    if isinstance(fund_code, str) or isinstance(fund_code, list):
        if isinstance(fund_code, str):
            fund_code = [fund_code]
        fund_where_condition = get_lst_condition(fund_code, 'fund_code')
        where.append(fund_where_condition)
    elif fund_code is None:
        fund_where_condition = '1 = 1'
    else:
        LOG.error('目前fund_code只支持str、list或者None，请按要求输入')
        raise NotImplementedError

    # 将日期转换成带杠字符串
    if start_date is None:
        start_date = EARLIEST_START_DATE
    else:
        start_date = time_tool.format_date_str(start_date)
    if end_date is None:
        end_date = time_tool.get_today(marker='with_n_dash')
    else:
        end_date = time_tool.format_date_str(end_date)
    if freq == 'M':
        start_date = start_date[:7]
        end_date = end_date[:7]

    # 只返回最新数据
    if only_latest:
        select = ', '.join(select)
        sql_str = (f"select {select} from {table} as a, "
                   f"(select fund_code as code, max({date_col}) as enddate "
                   f"from {table} "
                   f"where {date_col} <= '{end_date}' "
                   f"and {fund_where_condition} "
                   f"group by fund_code) as b "
                   f"where a.fund_code = b.code "
                   f"and a.{date_col} = b.enddate ")
        df = my_mysql.read_v3(sql_str, tmp_conn)
    # 返回一段时间数据的where语句
    else:
        where.append(f"{date_col} >= '{start_date}'")
        where.append(f"{date_col} <= '{end_date}'")
        df = my_mysql.read_v2(select=select, where=where, sfrom=table, conn=tmp_conn)

    if conn is None:
        tmp_conn.close()

    if df.empty:
        LOG.error(f"没有找到{fund_code}基金的数据，请检查")
        return

    if freq == 'D':
        df['end_date'] = df['end_date'].apply(time_tool.format_date_str)
    else:
        df['end_date'] = df['end_date'].apply(time_tool.get_the_end_of_this_month)

    return df


def get_fund_dividend_split_info(fund_code, start_date=None, end_date=None, only_latest=False):
    """
    得到基金的分红拆分信息

    :param str or list or None fund_code: 基金代码，支持单个(str)多个(list)全部(None)
    :param str or datetime.date or datetime.datetime or int start_date: 查询开始时间，支持带杠字符串、不带杠字符串、datetime、date、int格式
    :param str or datetime.date or datetime.datetime or int end_date: 查询结束时间，支持带杠字符串、不带杠字符串、datetime、date、int格式
    :param bool only_latest: True-只获取最新的一期数据，False-获取一个区间段的数据
    :return: pd.DataFrame，
                distribute_type：基金分配类型标识，-1=其他，1=分红，2=拆分
                distribution：分红/拆分比例， 1：X (分红为 1份基金份额分配多少元；拆分为 1份拆成几分)
                +-----------+------------+-----------------+--------------+
                | fund_code |  end_date  | distribute_type | distribution |
                +-----------+------------+-----------------+--------------+
                |   000001  | 2015-11-20 |        1        |     0.1      |
                |   000001  | 2016-12-26 |        1        |     0.03     |
                |   000001  | 2017-12-07 |        1        |     0.08     |
                |   000001  | 2019-11-06 |        1        |     0.05     |
                |   000001  | 2020-12-17 |        1        |     0.05     |
                +-----------+------------+-----------------+--------------+
    """
    select = ['fund_code', 'distribute_date as end_date', 'distribute_type', 'distribution']

    where = []

    # fund_code过滤条件
    if isinstance(fund_code, str) or isinstance(fund_code, list):
        if isinstance(fund_code, str):
            fund_code = [fund_code]
        fund_where_condition = get_lst_condition(fund_code, 'fund_code')
        where.append(fund_where_condition)
    elif fund_code is None:
        fund_where_condition = '1 = 1'
    else:
        LOG.error('目前fund_code只支持str、list或者None，请按要求输入')
        raise NotImplementedError

    # 将日期转换成带杠字符串
    if start_date is None:
        start_date = EARLIEST_START_DATE
    else:
        start_date = time_tool.format_date_str(start_date)
    if end_date is None:
        end_date = time_tool.get_today(marker='with_n_dash')
    else:
        end_date = time_tool.format_date_str(end_date)

    conn = db_conn.get_basic_data_conn()
    # 只返回最新数据
    if only_latest:
        select = ', '.join(select)
        sql_str = (f"select {select} from {T_FUND_DIVIDEND} as a, "
                   f"(select fund_code as code, max(distribute_date) as enddate "
                   f"from {T_FUND_DIVIDEND} "
                   f"where distribute_date <= '{end_date}' "
                   f"and {fund_where_condition} "
                   f"group by fund_code) as b "
                   f"where a.fund_code = b.code "
                   f"and a.distribute_date = b.enddate ")
        df = my_mysql.read_v3(sql_str, conn)
    # 返回一段时间数据的where语句
    else:
        where.append(f"distribute_date >= '{start_date}'")
        where.append(f"distribute_date <= '{end_date}'")
        df = my_mysql.read_v2(select=select, where=where, sfrom=T_FUND_DIVIDEND, conn=conn)

    conn.close()

    if df.empty:
        LOG.error(f"没有找到{fund_code}基金的数据，请检查")
        return

    df['end_date'] = df['end_date'].apply(time_tool.format_date_str)

    return df


def get_manager_nav(manager_code=None, fund_type=None, start_date=None, end_date=None,
                    only_latest=False, freq='D'):
    """
    获得基金经理的拟合净值

    :param str or list or None manager_code: 基金经理代码，支持单个（str）、多个（list）、所有（None）
    :param str or list or None fund_type: 管理基金类型，支持单个（str）、多个（list）、所有（None）
            00-综合型、10-股票型、20混合型、30-债券型、40-货币型、50-商品型、60-FOF型、70-QDII型
    :param str or datetime.date or datetime.datetime or int start_date: 开始时间，支持带杠字符串、不带杠字符串、datetime、date、int格式
    :param str or datetime.date or datetime.datetime or int end_date: 结束时间，支持带杠字符串、不带杠字符串、datetime、date、int格式
    :param bool only_latest: True-只获取最新的一天数据，False-获取一个区间段的数据
    :param str freq: D-日频；M-月频
    :return: pd.DataFrame
                +----------------------------------+-----------+------------+--------------+
                |           manager_code           | fund_type |  end_date  | adjusted_nav |
                +----------------------------------+-----------+------------+--------------+
                | 4579197ecf092c2a33fc6cea9da95ea6 |     00    | 2020-10-26 |    3.2827    |
                | 4579197ecf092c2a33fc6cea9da95ea6 |     00    | 2020-10-27 |    3.3362    |
                | 4579197ecf092c2a33fc6cea9da95ea6 |     00    | 2020-10-28 |    3.3589    |
                | 4579197ecf092c2a33fc6cea9da95ea6 |     00    | 2020-10-29 |    3.3812    |
                | 4579197ecf092c2a33fc6cea9da95ea6 |     00    | 2020-10-30 |    3.4006    |
                +----------------------------------+-----------+------------+--------------+
    """
    if freq == 'M':
        table = T_MANAGER_MONTHLY_RETURN
        date_col = '`month`'
        nav_col = 'nav_to as adjusted_nav'
    else:
        table = T_MANAGER_NAV
        date_col = 'nav_date'
        nav_col = 'adjusted_nav'

    select = ['manager_code', 'fund_type', f'{date_col} as end_date', nav_col]

    where = []

    # manager_code过滤条件
    if isinstance(manager_code, str) or isinstance(manager_code, list):
        if isinstance(manager_code, str):
            manager_code = [manager_code]
        manager_where_condition = get_lst_condition(manager_code, 'manager_code')
        where.append(manager_where_condition)
    elif manager_code is None:
        manager_where_condition = '1 = 1'
    else:
        LOG.error('目前manager_code只支持str、list或者None，请按要求输入')
        raise NotImplementedError

    # fund_type过滤条件
    if isinstance(fund_type, str) or isinstance(fund_type, list):
        if isinstance(fund_type, str):
            fund_type = [fund_type]
        type_where_condition = get_lst_condition(fund_type, 'fund_type')
        where.append(type_where_condition)
    elif fund_type is None:
        type_where_condition = '1 = 1'
    else:
        LOG.error('目前fund_type只支持str、list或者None，请按要求输入')
        raise NotImplementedError

    # 将日期转换成带杠字符串
    if start_date is None:
        start_date = EARLIEST_START_DATE
    else:
        start_date = time_tool.format_date_str(start_date)
    if end_date is None:
        end_date = time_tool.get_today(marker='with_n_dash')
    else:
        end_date = time_tool.format_date_str(end_date)

    if freq == 'M':
        start_date = start_date[:7]
        end_date = end_date[:7]

    conn = db_conn.get_derivative_data_conn()
    # 只返回最新数据
    if only_latest:
        select = ', '.join(select)
        sql_str = (f"select {select} from {table} as a, "
                   f"(select manager_code as code, fund_type as f_type, max({date_col}) as enddate "
                   f"from {table} "
                   f"where {date_col} <= '{end_date}' "
                   f"and {manager_where_condition} "
                   f"and {type_where_condition} "
                   f"group by manager_code, fund_type) as b "
                   f"where a.manager_code = b.code "
                   f"and a.fund_type = b.f_type "
                   f"and a.{date_col} = b.enddate ")
        df = my_mysql.read_v3(sql_str, conn)
    # 返回一段时间数据的where语句
    else:
        where.append(f"{date_col} >= '{start_date}'")
        where.append(f"{date_col} <= '{end_date}'")
        df = my_mysql.read_v2(select=select, where=where, sfrom=table, conn=conn)

    conn.close()

    if df.empty:
        LOG.error(f"没有找到{manager_code}基金经理的数据，请检查")
        return

    if freq == 'M':
        df['end_date'] = df['end_date'].apply(time_tool.get_the_end_of_this_month)
    else:
        df['end_date'] = df['end_date'].apply(time_tool.format_date_str)

    return df


def get_manager_return(manager_code=None, fund_type=None, start_date=None, end_date=None,
                       only_latest=False, freq='D', **kwargs):
    """
    获得基金经理的日收益率或者月收益率

    :param str or list or None manager_code: 基金经理代码，支持单个（str）、多个（list）、所有（None）
    :param str or list or None fund_type: 管理基金类型，支持单个（str）、多个（list）、所有（None）
            00-综合型、10-股票型、20混合型、30-债券型、40-货币型、50-商品型、60-FOF型、70-QDII型
    :param str or datetime.date or datetime.datetime or int start_date: 开始时间，
                支持带杠字符串、不带杠字符串、datetime、date、int格式
                若freq=M，只会读取日期的年份和月份
    :param str or datetime.date or datetime.datetime or int end_date: 结束时间，
                支持带杠字符串、不带杠字符串、datetime、date、int格式
                若freq=M，只会读取日期的年份和月份
    :param bool only_latest: True-只获取最新的一期数据，False-获取一个区间段的数据
    :param str freq: D-日收益率，返回列名daily_return；M-月收益率，返回列名monthly_return
    :param kwargs
            conn: 数据库连接
    :return: pd.DataFrame
                +----------------------------------+------------+-----------+--------------+
                |           manager_code           |  end_date  | fund_type | daily_return |
                +----------------------------------+------------+-----------+--------------+
                | 4579197ecf092c2a33fc6cea9da95ea6 | 2020-10-26 |     00    |    0.0223    |
                | 4579197ecf092c2a33fc6cea9da95ea6 | 2020-10-27 |     00    |    0.0163    |
                | 4579197ecf092c2a33fc6cea9da95ea6 | 2020-10-28 |     00    |    0.0068    |
                | 4579197ecf092c2a33fc6cea9da95ea6 | 2020-10-29 |     00    |    0.0066    |
                | 4579197ecf092c2a33fc6cea9da95ea6 | 2020-10-30 |     00    |    0.0057    |
                +----------------------------------+------------+-----------+--------------+
    """
    conn = kwargs.pop('conn', None)
    tmp_conn = db_conn.get_derivative_data_conn() if conn is None else conn

    if freq == 'D':
        table = T_MANAGER_DAILY_RETURN
        date_col = '`day`'
        ret_col = 'daily_return'
    elif freq == 'M':
        table = T_MANAGER_MONTHLY_RETURN
        date_col = '`month`'
        ret_col = 'monthly_return'
    else:
        raise NotImplementedError('目前freq只支持D和M')

    select = ['manager_code', f'{date_col} as end_date', 'fund_type', ret_col]

    where = []

    # manager_code过滤条件
    if isinstance(manager_code, str) or isinstance(manager_code, list):
        if isinstance(manager_code, str):
            manager_code = [manager_code]
        manager_where_condition = get_lst_condition(manager_code, 'manager_code')
        where.append(manager_where_condition)
    elif manager_code is None:
        manager_where_condition = '1 = 1'
    else:
        LOG.error('目前manager_code只支持str、list或者None，请按要求输入')
        raise NotImplementedError

    # fund_type过滤条件
    if isinstance(fund_type, str) or isinstance(fund_type, list):
        if isinstance(fund_type, str):
            fund_type = [fund_type]
        type_where_condition = get_lst_condition(fund_type, 'fund_type')
        where.append(type_where_condition)
    elif fund_type is None:
        type_where_condition = '1 = 1'
    else:
        LOG.error('目前fund_type只支持str、list或者None，请按要求输入')
        raise NotImplementedError

    # 将日期转换成带杠字符串
    if start_date is None:
        start_date = EARLIEST_START_DATE
    else:
        start_date = time_tool.format_date_str(start_date)
    if end_date is None:
        end_date = time_tool.get_today(marker='with_n_dash')
    else:
        end_date = time_tool.format_date_str(end_date)
    if freq == 'M':
        start_date = start_date[:7]
        end_date = end_date[:7]

    # 只返回最新数据
    if only_latest:
        select = ', '.join(select)
        sql_str = (f"select {select} from {table} as a, "
                   f"(select manager_code as code, fund_type as f_type, max({date_col}) as enddate "
                   f"from {table} "
                   f"where {date_col} <= '{end_date}' "
                   f"and {manager_where_condition} "
                   f"and {type_where_condition} "
                   f"group by manager_code, fund_type) as b "
                   f"where a.manager_code = b.code "
                   f"where a.fund_type = b.f_type "
                   f"and a.{date_col} = b.enddate ")
        df = my_mysql.read_v3(sql_str, tmp_conn)
    # 返回一段时间数据的where语句
    else:
        where.append(f"{date_col} >= '{start_date}'")
        where.append(f"{date_col} <= '{end_date}'")
        df = my_mysql.read_v2(select=select, where=where, sfrom=table, conn=tmp_conn)

    if conn is None:
        tmp_conn.close()

    if df.empty:
        LOG.error(f"没有找到{manager_code}基金经理的数据，请检查")
        return

    if freq == 'D':
        df['end_date'] = df['end_date'].apply(time_tool.format_date_str)
    else:
        df['end_date'] = df['end_date'].apply(time_tool.get_the_end_of_this_month)

    return df


def get_company_nav(company_code=None, fund_type=None, start_date=None, end_date=None,
                    only_latest=False, freq='D'):
    """
    获得基金公司的拟合净值

    :param str or list or None company_code: 基金经理代码，支持单个（str）、多个（list）、所有（None）
    :param str or list or None fund_type: 管理基金类型，支持单个（str）、多个（list）、所有（None）
            00-综合型、10-股票型、20混合型、30-债券型、40-货币型、50-商品型、60-FOF型、70-QDII型
    :param str or datetime.date or datetime.datetime or int start_date: 开始时间，支持带杠字符串、不带杠字符串、datetime、date、int格式
    :param str or datetime.date or datetime.datetime or int end_date: 结束时间，支持带杠字符串、不带杠字符串、datetime、date、int格式
    :param bool only_latest: True-只获取最新的一天数据，False-获取一个区间段的数据
    :param str freq: D-日频；M-月频
    :return: pd.DataFrame
                +----------------------------------+-----------+------------+--------------+
                |           company_code           | fund_type |  end_date  | adjusted_nav |
                +----------------------------------+-----------+------------+--------------+
                | 187a7a5e9e7c70b868198a6f72303c97 |     00    | 2020-10-26 |    1.0656    |
                | 187a7a5e9e7c70b868198a6f72303c97 |     00    | 2020-10-27 |    1.0656    |
                | 187a7a5e9e7c70b868198a6f72303c97 |     00    | 2020-10-28 |    1.0657    |
                | 187a7a5e9e7c70b868198a6f72303c97 |     00    | 2020-10-29 |    1.0659    |
                | 187a7a5e9e7c70b868198a6f72303c97 |     00    | 2020-10-30 |    1.0659    |
                +----------------------------------+-----------+------------+--------------+
    """
    if freq == 'M':
        table = T_COMPANY_MONTHLY_RETURN
        date_col = '`month`'
        nav_col = 'nav_to as adjusted_nav'
    else:
        table = T_COMPANY_NAV
        date_col = 'nav_date'
        nav_col = 'adjusted_nav'

    select = ['company_code', 'fund_type', f'{date_col} as end_date', nav_col]

    where = []

    # company_code过滤条件
    if isinstance(company_code, str) or isinstance(company_code, list):
        if isinstance(company_code, str):
            company_code = [company_code]
        company_where_condition = get_lst_condition(company_code, 'company_code')
        where.append(company_where_condition)
    elif company_code is None:
        company_where_condition = '1 = 1'
    else:
        LOG.error('目前company_code只支持str、list或者None，请按要求输入')
        raise NotImplementedError

    # fund_type过滤条件
    if isinstance(fund_type, str) or isinstance(fund_type, list):
        if isinstance(fund_type, str):
            fund_type = [fund_type]
        type_where_condition = get_lst_condition(fund_type, 'fund_type')
        where.append(type_where_condition)
    elif fund_type is None:
        type_where_condition = '1 = 1'
    else:
        LOG.error('目前fund_type只支持str、list或者None，请按要求输入')
        raise NotImplementedError

    # 将日期转换成带杠字符串
    if start_date is None:
        start_date = EARLIEST_START_DATE
    else:
        start_date = time_tool.format_date_str(start_date)
    if end_date is None:
        end_date = time_tool.get_today(marker='with_n_dash')
    else:
        end_date = time_tool.format_date_str(end_date)

    if freq == 'M':
        start_date = start_date[:7]
        end_date = end_date[:7]

    conn = db_conn.get_derivative_data_conn()
    # 只返回最新数据
    if only_latest:
        select = ', '.join(select)
        sql_str = (f"select {select} from {table} as a, "
                   f"(select company_code as code, fund_type as f_type, max({date_col}) as enddate "
                   f"from {table} "
                   f"where {date_col} <= '{end_date}' "
                   f"and {company_where_condition} "
                   f"and {type_where_condition} "
                   f"group by company_code, fund_type) as b "
                   f"where a.company_code = b.code "
                   f"and a.fund_type = b.f_type "
                   f"and a.{date_col} = b.enddate ")
        df = my_mysql.read_v3(sql_str, conn)
    # 返回一段时间数据的where语句
    else:
        where.append(f"{date_col} >= '{start_date}'")
        where.append(f"{date_col} <= '{end_date}'")
        df = my_mysql.read_v2(select=select, where=where, sfrom=table, conn=conn)

    conn.close()

    if df.empty:
        LOG.error(f"没有找到{company_code}基金公司的数据，请检查")
        return

    if freq == 'M':
        df['end_date'] = df['end_date'].apply(time_tool.get_the_end_of_this_month)
    else:
        df['end_date'] = df['end_date'].apply(time_tool.format_date_str)

    return df


def get_company_return(company_code=None, fund_type=None, start_date=None, end_date=None,
                       only_latest=False, freq='D', **kwargs):
    """
    获得基金公司的日收益率或者月收益率

    :param str or list or None company_code: 基金公司代码，支持单个（str）、多个（list）、所有（None）
    :param str or list or None fund_type: 管理基金类型，支持单个（str）、多个（list）、所有（None）
            00-综合型、10-股票型、20混合型、30-债券型、40-货币型、50-商品型、60-FOF型、70-QDII型
    :param str or datetime.date or datetime.datetime or int start_date: 开始时间，
                支持带杠字符串、不带杠字符串、datetime、date、int格式
                若freq=M，只会读取日期的年份和月份
    :param str or datetime.date or datetime.datetime or int end_date: 结束时间，
                支持带杠字符串、不带杠字符串、datetime、date、int格式
                若freq=M，只会读取日期的年份和月份
    :param bool only_latest: True-只获取最新的一期数据，False-获取一个区间段的数据
    :param str freq: D-日收益率，返回列名daily_return；M-月收益率，返回列名monthly_return
    :param kwargs
            conn: 数据库连接
    :return: pd.DataFrame
                +----------------------------------+------------+-----------+--------------+
                |           company_code           |  end_date  | fund_type | daily_return |
                +----------------------------------+------------+-----------+--------------+
                | 187a7a5e9e7c70b868198a6f72303c97 | 2020-10-26 |     00    |    0.0002    |
                | 187a7a5e9e7c70b868198a6f72303c97 | 2020-10-27 |     00    |    0.0001    |
                | 187a7a5e9e7c70b868198a6f72303c97 | 2020-10-28 |     00    |    0.0001    |
                | 187a7a5e9e7c70b868198a6f72303c97 | 2020-10-29 |     00    |    0.0001    |
                | 187a7a5e9e7c70b868198a6f72303c97 | 2020-10-30 |     00    |    0.0001    |
                +----------------------------------+------------+-----------+--------------+
    """
    conn = kwargs.pop('conn', None)
    tmp_conn = db_conn.get_derivative_data_conn() if conn is None else conn

    if freq == 'D':
        table = T_COMPANY_DAILY_RETURN
        date_col = '`day`'
        ret_col = 'daily_return'
    elif freq == 'M':
        table = T_COMPANY_MONTHLY_RETURN
        date_col = '`month`'
        ret_col = 'monthly_return'
    else:
        raise NotImplementedError('目前freq只支持D和M')

    select = ['company_code', f'{date_col} as end_date', 'fund_type', ret_col]

    where = []

    # company_code过滤条件
    if isinstance(company_code, str) or isinstance(company_code, list):
        if isinstance(company_code, str):
            company_code = [company_code]
        company_where_condition = get_lst_condition(company_code, 'company_code')
        where.append(company_where_condition)
    elif company_code is None:
        company_where_condition = '1 = 1'
    else:
        LOG.error('目前company_code只支持str、list或者None，请按要求输入')
        raise NotImplementedError

    # fund_type过滤条件
    if isinstance(fund_type, str) or isinstance(fund_type, list):
        if isinstance(fund_type, str):
            fund_type = [fund_type]
        type_where_condition = get_lst_condition(fund_type, 'fund_type')
        where.append(type_where_condition)
    elif fund_type is None:
        type_where_condition = '1 = 1'
    else:
        LOG.error('目前fund_type只支持str、list或者None，请按要求输入')
        raise NotImplementedError

    # 将日期转换成带杠字符串
    if start_date is None:
        start_date = EARLIEST_START_DATE
    else:
        start_date = time_tool.format_date_str(start_date)
    if end_date is None:
        end_date = time_tool.get_today(marker='with_n_dash')
    else:
        end_date = time_tool.format_date_str(end_date)
    if freq == 'M':
        start_date = start_date[:7]
        end_date = end_date[:7]

    # 只返回最新数据
    if only_latest:
        select = ', '.join(select)
        sql_str = (f"select {select} from {table} as a, "
                   f"(select company_code as code, fund_type as f_type, max({date_col}) as enddate "
                   f"from {table} "
                   f"where {date_col} <= '{end_date}' "
                   f"and {company_where_condition} "
                   f"and {type_where_condition} "
                   f"group by company_code, fund_type) as b "
                   f"where a.company_code = b.code "
                   f"where a.fund_type = b.f_type "
                   f"and a.{date_col} = b.enddate ")
        df = my_mysql.read_v3(sql_str, tmp_conn)
    # 返回一段时间数据的where语句
    else:
        where.append(f"{date_col} >= '{start_date}'")
        where.append(f"{date_col} <= '{end_date}'")
        df = my_mysql.read_v2(select=select, where=where, sfrom=table, conn=tmp_conn)

    if conn is None:
        tmp_conn.close()

    if df.empty:
        LOG.error(f"没有找到{company_code}基金公司的数据，请检查")
        return

    if freq == 'D':
        df['end_date'] = df['end_date'].apply(time_tool.format_date_str)
    else:
        df['end_date'] = df['end_date'].apply(time_tool.get_the_end_of_this_month)

    return df


def get_performance_indicator_data(indicator, asset_type, freq, asset_code, period_interval,
                                   start_date=None, end_date=None, fund_type=None):
    """
    读取业绩指标数据

    :param str indicator: 业绩指标，如'stddev','annual_ret'等，目前暂时只支持单指标，
                          可选'ret','risk','riskadjret','offanddef','stocktiming'这几个表中的指标
                    ret-不同时间区间收益率
                    annual_ret-不同时间区间年化收益率
                    active_ret-不同时间区间主动收益率
                    annual_active_ret-不同时间区间年化主动收益率
                    winning_rate-不同时间区间日胜率
                    ret_persistence_score-不同时间区间业绩持续性得分
                    ret_persistence_rank-不同时间区间业绩持续性等级
                    ret_management-不同时间区间收益管理能力
                    stddev-不同时间区间波动率（年化）
                    downsidedev-不同时间区间下行风险（年化）
                    maxdrawdown-不同时间区间回撤
                    skewness-不同时间区间偏度
                    kurtosis-不同时间区间峰度
                    corr_coef-不同时间区间相关系数
                    beta-不同时间区间贝塔
                    trackerror-不同时间区间跟踪误差（年化）
                    risk_management-不同时间区间风险管理能力
                    sharperatio-不同时间区间夏普比率
                    sortinoratio-不同时间区间索提诺比率
                    treynorratio-不同时间区间特雷诺比率
                    calmarratio-不同时间区间卡玛比率
                    omegaratio-不同时间区间欧米茄比率
                    kapparatio-不同时间区间卡帕比率
                    m2-不同时间区间M2
                    inforatio-不同时间区间信息比率
                    jensen_alpha-不同时间区间詹森阿尔法
                    annual_jensen_alpha-不同时间区间年化詹森阿尔法
                    riskadjret_management-风险调整后收益管理
                    beat_market_ability-超越大盘能力
                    ucratio-上行捕获率
                    dcratio-下行捕获率
                    ucret-几何平均上行捕获收益率
                    dcret-几何平均下行捕获收益率
                    offense_defense-不同时间区间攻守风格
                    stock_ability_tm-不同时间区间选股能力
                    timing_ability_tm-不同时间区间择时能力
                    stock_ability_pvalue_tm-不同时间区间选股能力统计检验
                    timing_ability_pvalue_tm-不同时间区间择时能力统计检验
                    stock_ability_rank_tm-不同时间区间选股能力等级
                    timing_ability_rank_tm-不同时间区间择时能力等级
                    stock_ability_hm-不同时间区间选股能力
                    timing_ability_hm-不同时间区间择时能力
                    stock_ability_pvalue_hm-不同时间区间选股能力统计检验
                    timing_ability_pvalue_hm-不同时间区间择时能力统计检验
                    stock_ability_rank_hm-不同时间区间选股能力等级
                    timing_ability_rank_hm-不同时间区间择时能力等级
                    stock_ability_cl-不同时间区间选股能力
                    timing_ability_cl-不同时间区间择时能力
                    stock_ability_pvalue_cl-不同时间区间选股能力统计检验
                    timing_ability_pvalue_cl-不同时间区间择时能力统计检验
                    stock_ability_rank_cl-不同时间区间选股能力等级
                    timing_ability_rank_cl-不同时间区间择时能力等级
    :param str or list or None asset_code: 基金、基金经理、基金公司、指数代码。支持单个（str）、多个（list）、全部（None）
    :param str asset_type: 类型，目前支持'fund'-基金,'manager'-基金经理,'company'-基金公司,'index'-指数
    :param str freq: 数据频率，可选'D','M'
    :param int or list or None period_interval: period_interval的过滤条件，支持单个（int）、多个（list）、全部（None）
    :param str or datetime.date or datetime.datetime or int start_date: 开始时间，
                支持带杠字符串、不带杠字符串、datetime、date、int格式
                若freq=M，会将日期变为当月最后一天
    :param str or datetime.date or datetime.datetime or int end_date: 结束时间，
                支持带杠字符串、不带杠字符串、datetime、date、int格式
                若freq=M，会将日期变为当月最后一天
    :param str or list or None fund_type: 基金经理或基金公司的独有字段，支持单个（str）、多个（list）、全部（None）
    :return: pd.DataFrame，包括asset_code, start_date, end_date, period_interval, index_code, indicator_data, fund_type(针对基金经理和基金公司)
                +----------------------------------+------------+------------+-----------------+--------+------------+-----------+
                |           manager_code           | start_date |  end_date  | period_interval |  ret   | index_code | fund_type |
                +----------------------------------+------------+------------+-----------------+--------+------------+-----------+
                | 1fdcb8eb060057722d40512c46312fd1 | 2019-07-31 | 2020-07-31 |        5        | 1.2596 |   TK00ZH   |     00    |
                | 1fdcb8eb060057722d40512c46312fd1 | 2019-08-31 | 2020-08-31 |        5        | 1.0219 |   TK00ZH   |     00    |
                | 4579197ecf092c2a33fc6cea9da95ea6 | 2019-07-31 | 2020-07-31 |        5        | 1.3192 |   TK00ZH   |     00    |
                | 4579197ecf092c2a33fc6cea9da95ea6 | 2019-08-31 | 2020-08-31 |        5        | 0.969  |   TK00ZH   |     00    |
                +----------------------------------+------------+------------+-----------------+--------+------------+-----------+
    """
    if asset_type == 'fund':
        code_type = 'fund_code'
        table_name_part_1 = 'fnd'
    elif asset_type == 'manager':
        code_type = 'manager_code'
        table_name_part_1 = 'mgr'
    elif asset_type == 'company':
        code_type = 'company_code'
        table_name_part_1 = 'comp'
    elif asset_type == 'index':
        code_type = 'index_code'
        table_name_part_1 = 'idx'
    else:
        LOG.error('目前asset_type只支持fund、manager、company、index，请按要求输入')
        raise NotImplementedError

    if indicator in ['ret', 'annual_ret', 'active_ret', 'annual_active_ret',
                     'winning_rate', 'ret_persistence_score', 'ret_persistence_rank', 'ret_management']:
        table_name_part_2 = 'retstats'
    elif indicator in ['stddev', 'downsidedev', 'maxdrawdown', 'skewness',
                       'kurtosis', 'corr_coef', 'beta', 'trackerror', 'risk_management']:
        table_name_part_2 = 'riskstats'
    elif indicator in ['sharperatio', 'sortinoratio', 'treynorratio', 'calmarratio', 'omegaratio',
                       'kapparatio', 'm2', 'inforatio', 'jensen_alpha', 'annual_jensen_alpha',
                       'riskadjret_management', 'beat_market_ability']:
        table_name_part_2 = 'riskadjretstats'
    elif indicator in ['ucratio', 'dcratio', 'ucret', 'dcret', 'offense_defense']:
        table_name_part_2 = 'offanddef'
    elif indicator in ['stock_ability_tm', 'timing_ability_tm', 'stock_ability_pvalue_tm', 'timing_ability_pvalue_tm',
                       'stock_ability_rank_tm', 'timing_ability_rank_tm', 'stock_ability_hm', 'timing_ability_hm',
                       'stock_ability_pvalue_hm', 'timing_ability_pvalue_hm', 'stock_ability_rank_hm',
                       'timing_ability_rank_hm', 'stock_ability_cl', 'timing_ability_cl', 'stock_ability_pvalue_cl',
                       'timing_ability_pvalue_cl', 'stock_ability_rank_cl', 'timing_ability_rank_cl',
                       'security_choosing_ability', 'security_choosing_ability_pvalue', 'timing_ability',
                       'timing_ability_pvalue']:
        table_name_part_2 = 'stocktiming'
    else:
        LOG.error('输入的指标不在目前已计算的指标中，请重新输入')
        raise NotImplementedError

    if freq == 'D':
        freq = 'day'
    elif freq == 'M':
        freq = 'month'
    else:
        raise NotImplementedError('目前freq只支持D、M，请按要求输入')

    read_table = 'mf_di_' + table_name_part_1 + table_name_part_2 + '_' + freq

    select = [code_type, 'start_date', 'end_date', 'period_interval', indicator]
    if (asset_type == 'fund') or (asset_type in ['manager', 'company'] and table_name_part_2 != 'stocktiming'):
        select.append('index_code')
    if asset_type in ['manager', 'company']:
        select.append('fund_type')

    conn = db_conn.get_derivative_data_conn()

    where = []
    if isinstance(asset_code, str):
        where.append(f'{code_type} = \'{asset_code}\'')
    elif isinstance(asset_code, list):
        where.append(get_lst_condition(asset_code, code_type))
    elif asset_code is None:
        pass
    else:
        LOG.error('目前asset_code只支持str、list或者None，请按要求输入')
        raise NotImplementedError

    # 将日期转换成带杠字符串
    if start_date is None:
        start_date = EARLIEST_START_DATE
    else:
        start_date = time_tool.format_date_str(start_date)
    if end_date is None:
        end_date = time_tool.get_today(marker='with_n_dash')
    else:
        end_date = time_tool.format_date_str(end_date)
    # 如果是月频，将日期转换成当月最后一天
    if freq == 'M':
        start_date = time_tool.get_the_end_of_this_month(start_date)
        end_date = time_tool.get_the_end_of_this_month(end_date)

    where.append(f'end_date >= \'{start_date}\'')
    where.append(f'end_date <= \'{end_date}\'')

    # period_interval过滤条件
    if isinstance(period_interval, int):
        where.append(f'period_interval=\'{period_interval}\'')
    elif isinstance(period_interval, list):
        where.append(get_lst_condition(period_interval, 'period_interval'))
    elif period_interval is None:
        pass
    else:
        LOG.error('目前period_interval只支持str、list或者None，请按要求输入')
        raise NotImplementedError

    # fund_type过滤条件
    if isinstance(fund_type, str) and asset_type in ['manager', 'company']:
        where.append(f'fund_type = \'{fund_type}\'')
    elif isinstance(fund_type, list) and asset_type in ['manager', 'company']:
        where.append(get_lst_condition(fund_type, 'fund_type'))
    elif fund_type is None:
        pass
    else:
        LOG.error('目前fund_type只支持str、list或者None，请按要求输入')
        raise NotImplementedError

    df = my_mysql.read_v2(select=select, where=where, sfrom=read_table, conn=conn)
    conn.close()

    if df.empty:
        LOG.error(f"没有找到类型为{asset_type}，代码为{asset_code}的{indicator}数据，"
                  f"请去{read_table}表检查数据")
        return

    # 目前表中有脏数据，start_date为null，需要剔除
    df = df.dropna(subset=['start_date'])
    df[['start_date', 'end_date']] = df[['start_date', 'end_date']].applymap(time_tool.format_date_str)

    return df


def get_fund_rating(fund_code=None, start_date=None, end_date=None, period_interval=None):
    """
    获得基金的评分数据

    :param str or list or None fund_code: 基金代码，支持单个（str）、多个（list）、所有（None）
    :param str or datetime.date or datetime.datetime or int start_date: 开始时间，
                支持带杠字符串、不带杠字符串、datetime、date、int格式
                若freq=M，只会读取日期的年份和月份
    :param str or datetime.date or datetime.datetime or int end_date: 结束时间，
                支持带杠字符串、不带杠字符串、datetime、date、int格式
                若freq=M，只会读取日期的年份和月份
    :param int or list or None period_interval: period_interval的过滤条件，支持单个（int）、多个（list）、全部（None）
    :return: pd.DataFrame
                tkrar_score_type_1: 基金在一级分类中的得分
                tkrar_score_type_2: 基金在二级分类中的得分
                tkrar_score_type_all: 基金在所有基金中的得分
                tkrar_star_type_1: 基金在一级分类中的评级
                tkrar_star_type_2: 基金在二级分类中的评级
                tkrar_star_type_all: 基金在所有基金中的评级
        +-----------+------------+-----------------+--------------------+--------------------+----------------------+-------------------+-------------------+---------------------+
        | fund_code |  end_date  | period_interval | tkrar_score_type_1 | tkrar_score_type_2 | tkrar_score_type_all | tkrar_star_type_1 | tkrar_star_type_2 | tkrar_star_type_all |
        +-----------+------------+-----------------+--------------------+--------------------+----------------------+-------------------+-------------------+---------------------+
        |   003095  | 2021-02-28 |        5        |      90.2878       |       86.631       |       92.4771        |    ★★★★★       |     ★★★★       |      ★★★★★       |
        |   110022  | 2021-02-28 |        5        |      89.2683       |      89.2683       |       97.1076        |     ★★★★       |     ★★★★       |       ★★★★★       |
        +-----------+------------+-----------------+--------------------+--------------------+----------------------+-------------------+-------------------+---------------------+

    """
    conn = db_conn.get_derivative_data_conn()

    select = ['fund_code', f'end_date', 'period_interval',
              'tkrar_score_type_1', 'tkrar_score_type_2', 'tkrar_score_type_all',
              'tkrar_star_type_1', 'tkrar_star_type_2', 'tkrar_star_type_all']

    where = []

    # fund_code过滤条件
    if isinstance(fund_code, str) or isinstance(fund_code, list):
        if isinstance(fund_code, str):
            fund_code = [fund_code]
        fund_where_condition = get_lst_condition(fund_code, 'fund_code')
        where.append(fund_where_condition)
    elif fund_code is None:
        pass
    else:
        LOG.error('目前fund_code只支持str、list或者None，请按要求输入')
        raise NotImplementedError

    # period_interval过滤条件
    if isinstance(period_interval, int):
        where.append(f'period_interval=\'{period_interval}\'')
    elif isinstance(period_interval, list):
        where.append(get_lst_condition(period_interval, 'period_interval'))
    elif period_interval is None:
        pass
    else:
        LOG.error('目前period_interval只支持str、list或者None，请按要求输入')
        raise NotImplementedError

    # 将日期转换成带杠字符串
    if start_date is None:
        start_date = EARLIEST_START_DATE
    else:
        start_date = time_tool.format_date_str(start_date)
    if end_date is None:
        end_date = time_tool.get_today(marker='with_n_dash')
    else:
        end_date = time_tool.format_date_str(end_date)
    where.append(f"end_date >= '{start_date}'")
    where.append(f"end_date <= '{end_date}'")

    df = my_mysql.read_v2(select=select, where=where, sfrom=T_FUND_RATING, conn=conn)
    conn.close()

    if df.empty:
        LOG.error(f"没有找到{fund_code}基金的数据，请检查")
        return

    df['end_date'] = df['end_date'].apply(time_tool.format_date_str)

    return df


"""
之前的API函数，目前都替换成最新的API函数，以下函数已弃用
"""

# C_CUM_NAV = 'adjusted_nav'
# C_DATE_CUM_NAV = 'nav_date'
# C_P_DAILY = 'daily_return'
# C_DATE_DAILY = '`day`'
# C_P_MONTHLY = 'monthly_return'
# C_DATE_MONTHLY = '`month`'
# C_C = 'fund_code'
# C_S_DATE = 'start_date'
# C_E_DATE = 'end_date'
# C_RETURN_PER_10K = 'return_pertt'
# C_NAV = 'nav'
# M_C = 'manager_code'
# C_F_T = 'fund_type'


# @common_wrappers.rename_date_col_to_tj(C_E_DATE)
# @common_wrappers.automatic_date_formatter_wrapper({
#     C_E_DATE: {'fmt': time_tool.PY_FMT_WITH_N_DASH, 'class': 'normal_date'}})
# @conn_wrappers.conn_meddler(conn_getter=db_conn.get_basic_data_conn)
# def get_fund_net_value(fund_code, date_related_filter=None, return_only_nav=True, **kwargs):
#     """
#     得到基金的净值数据
#
#     :param fund_code: 基金代码，支持单个(str)多个(list)全部(None)
#     :param date_related_filter: 日期过滤条件，格式 [('end_date', '2020-01-01', '2020-03-01')]
#     :param return_only_nav: 只返回净值字段，默认True
#     :param kwargs:
#     :return:
#     """
#     select = C_NAV if return_only_nav else [C_C, C_S_DATE, C_E_DATE, C_NAV, C_RETURN_PER_10K]
#     where = []
#     if isinstance(fund_code, str):
#         where.append(f'{C_C}=\'{fund_code}\'')
#     elif isinstance(fund_code, list):
#         where.append(get_lst_condition(fund_code, 'fund_code'))
#     elif fund_code is None:
#         pass
#     else:
#         LOG.error('目前x只支持str、list或者None，请按要求输入')
#         raise NotImplementedError
#     if date_related_filter is not None:
#         where.append(date_related_filter)
#
#     df = my_mysql.read_v2(
#         select=select, where=where, sfrom=T_FUND_ORIGIN_NAV,
#         conn=kwargs.pop('conn')
#     )
#
#     return df


# @common_wrappers.automatic_date_formatter_wrapper({
#     C_DATE_CUM_NAV: {'fmt': time_tool.PY_FMT_WITH_N_DASH, 'class': 'normal_date'}})
# def get_fund_adj_nav(fund_code=None, date_related_filter=None, order_by=None, **kwargs):
#     """
#     获得基金的复权净值
#
#     :param fund_code: 基金代码，支持单个(str)多个(list)全部(None)
#     :param date_related_filter: 日期过滤条件，格式 [('end_date', '2020-01-01', '2020-03-01')]
#     :param order_by: 按哪个字段(str)或哪几个字段(list)升序排列
#     :param kwargs:
#         - return_only_nav: 只返回净值字段，默认True
#     :return:
#     """
#     conn = kwargs.pop('conn', None)
#     return_only_nav = kwargs.pop('return_only_nav', True)
#
#     close_conn = False
#     if conn is None:
#         conn = db_conn.get_basic_data_conn()
#         close_conn = True
#
#     select = C_CUM_NAV if return_only_nav else [C_DATE_CUM_NAV, C_C, C_CUM_NAV]
#     where = []
#     if isinstance(fund_code, str):
#         where.append(f'{C_C}=\'{fund_code}\'')
#     elif isinstance(fund_code, list):
#         where.append(get_lst_condition(fund_code, 'fund_code'))
#     elif fund_code is None:
#         pass
#     else:
#         LOG.error('目前fund_code只支持str、list或者None，请按要求输入')
#         raise NotImplementedError
#     if date_related_filter is not None:
#         where.append(date_related_filter)
#     df = my_mysql.read_v2(
#         select=select, where=where, sfrom=T_FUND_ADJ_NAV, order_by=order_by,
#         conn=conn
#     )
#     if close_conn:
#         conn.close()
#     return df


# def get_fund_daily_return(fund_code, start=None, end=None, order_by=None, **kwargs):
#     """
#     得到基金的日收益率
#
#     :param fund_code: 基金代码，支持单个(str)多个(list)全部(None)
#     :param str start: 查询开始时间，格式'2020-08-01'，若只查询一天，start和end请输入同一个日期
#     :param str end: 查询结束时间，格式'2020-09-01'
#     :param order_by: 按哪个字段(str)或哪几个字段(list)升序排列
#     :param kwargs:
#         - return_only_price_col: 只返回收益率字段，默认True，请不要修改默认值，以免影响其他脚本
#         - conn: 数据库连接
#         - replace_tj: 是否将tj字段名称修改为trade_date，请不要修改默认值，以免影响其他脚本
#         - replace_price: 是否将f_price字段名称修改为daily_return，请不要修改默认值，以免影响其他脚本
#     :return:
#     """
#     return_only_price_col = kwargs.pop('return_only_price_col', True)
#     conn = kwargs.pop('conn', None)
#     replace_tj = kwargs.pop('replace_tj', False)
#     replace_price = kwargs.pop('replace_price', False)
#
#     tmp_conn = db_conn.get_derivative_data_conn() if conn is None else conn
#     c_price = f'{C_P_DAILY} as f_price'
#     where = []
#
#     if return_only_price_col:
#         select = c_price
#     else:
#         select = [f'{C_DATE_DAILY} as tj', C_C, c_price]
#
#     if isinstance(fund_code, str):
#         where.append(f'{C_C}=\'{fund_code}\'')
#     elif isinstance(fund_code, list):
#         where.append(get_lst_condition(fund_code, 'fund_code'))
#     elif fund_code is None:
#         pass
#     else:
#         LOG.error('目前x只支持str、list或者None，请按要求输入')
#         raise NotImplementedError
#
#     if start is not None:
#         where.append(f'{C_DATE_DAILY}>=\'{start}\'')
#     if end is not None:
#         where.append(f'{C_DATE_DAILY}<=\'{end}\'')
#     df = my_mysql.read_v2(
#         select=select, where=where, sfrom=T_FUND_DAILY_RETURN,
#         order_by=order_by, conn=tmp_conn
#     )
#     if conn is None:
#         tmp_conn.close()
#
#     if replace_tj:
#         df = df.rename(columns={'tj': 'trade_date'})
#     if replace_price:
#         df = df.rename(columns={'f_price': 'daily_return'})
#
#     return df


# def get_fund_monthly_return(fund_code, start=None, end=None, order_by=None, **kwargs):
#     """
#     得到基金的月收益率
#
#     :param fund_code: 基金代码，支持单个(str)多个(list)全部(None)
#     :param str start: 查询开始时间，格式'2020-08'，若只查询一天，start和end请输入同一个日期
#     :param str end: 查询结束时间，格式'2020-09'
#     :param order_by: 按哪个字段(str)或哪几个字段(list)升序排列
#     :param kwargs:
#         - return_only_price_col: 只返回收益率字段，默认True，请不要修改默认值，以免影响其他脚本
#         - conn: 数据库连接
#         - replace_tj: 是否将tj字段名称修改为trade_date，请不要修改默认值，以免影响其他脚本
#         - replace_price: 是否将f_price字段名称修改为monthly_return，请不要修改默认值，以免影响其他脚本
#     :return:
#     """
#     return_only_price_col = kwargs.pop('return_only_price_col', True)
#     conn = kwargs.pop('conn', None)
#     replace_tj = kwargs.pop('replace_tj', False)
#     replace_price = kwargs.pop('replace_price', False)
#
#     tmp_conn = db_conn.get_derivative_data_conn() if conn is None else conn
#     c_price = f'{C_P_MONTHLY} as f_price'
#     where = []
#     if return_only_price_col:
#         select = c_price
#     else:
#         select = [f'{C_DATE_MONTHLY} as tj', C_C, c_price]
#
#     if isinstance(fund_code, str):
#         where.append(f'{C_C}=\'{fund_code}\'')
#     elif isinstance(fund_code, list):
#         where.append(get_lst_condition(fund_code, 'fund_code'))
#     elif fund_code is None:
#         pass
#     else:
#         LOG.error('目前x只支持str、list或者None，请按要求输入')
#         raise NotImplementedError
#     where.append('special_sign = 0')
#
#     if start is not None:
#         where.append(f'{C_DATE_MONTHLY}>=\'{start}\'')
#     if end is not None:
#         where.append(f'{C_DATE_MONTHLY}<=\'{end}\'')
#     df = my_mysql.read_v2(
#         select=select, where=where, sfrom=T_FUND_MONTHLY_RETURN,
#         order_by=order_by, conn=tmp_conn
#     )
#     if conn is None:
#         tmp_conn.close()
#
#     if replace_tj:
#         df = df.rename(columns={'tj': 'trade_date'})
#     if replace_price:
#         df = df.rename(columns={'f_price': 'monthly_return'})
#
#     return df


# def get_manager_adj_nav(manager_code=None, start=None, end=None, fund_type='00', order_by=None, **kwargs):
#     """
#     得到基金经理的拟合净值
#
#     :param manager_code: 基金经理代码，支持单个(str)多个(list)全部(None)
#     :param str start: 查询开始时间，格式'2020-08-01'，若只查询一天，start和end请输入同一个日期
#     :param str end: 查询结束时间，格式'2020-09-01'
#     :param str fund_type: 管理基金类型，默认'00'-全部
#     :param order_by: 按哪个字段(str)或哪几个字段(list)升序排列
#     :param kwargs:
#         - conn: 数据库连接
#         - replace_tj: 是否将tj字段名称修改为trade_date，请不要修改默认值，以免影响其他脚本
#     :return:
#     """
#     conn = kwargs.pop('conn', None)
#     replace_tj = kwargs.pop('replace_tj', False)
#
#     tmp_conn = db_conn.get_derivative_data_conn() if conn is None else conn
#     where = []
#
#     select = ['manager_code', 'fund_type', 'nav_date as tj', C_CUM_NAV]
#
#     if isinstance(manager_code, str):
#         where.append(f'{M_C}=\'{manager_code}\'')
#     elif isinstance(manager_code, list):
#         where.append(get_lst_condition(manager_code, M_C))
#     elif manager_code is None:
#         pass
#     else:
#         LOG.error('目前manager_code只支持str、list或者None，请按要求输入')
#         raise NotImplementedError
#
#     where.append(f'{C_F_T}=\'{fund_type}\'')
#
#     if start is not None:
#         where.append(f'nav_date>=\'{start}\'')
#     if end is not None:
#         where.append(f'nav_date<=\'{end}\'')
#     df = my_mysql.read_v2(
#         select=select, where=where, sfrom=T_MANAGER_NAV,
#         order_by=order_by, conn=tmp_conn
#     )
#     if conn is None:
#         tmp_conn.close()
#
#     if replace_tj:
#         df = df.rename(columns={'tj': 'trade_date'})
#
#     return df


# def get_manager_daily_return(manager_code=None, start=None, end=None, fund_type='00', order_by=None, **kwargs):
#     """
#     得到基金经理的日收益率
#
#     :param manager_code: 基金经理代码，支持单个(str)多个(list)全部(None)
#     :param str start: 查询开始时间，格式'2020-08-01'，若只查询一天，start和end请输入同一个日期
#     :param str end: 查询结束时间，格式'2020-09-01'
#     :param str fund_type: 管理基金类型，默认'00'-全部
#     :param order_by: 按哪个字段(str)或哪几个字段(list)升序排列
#     :param kwargs:
#         - return_only_price_col: 只返回收益率字段，默认True，请不要修改默认值，以免影响其他脚本
#         - conn: 数据库连接
#         - replace_tj: 是否将tj字段名称修改为trade_date，请不要修改默认值，以免影响其他脚本
#         - replace_price: 是否将chg字段名称修改为daily_return，请不要修改默认值，以免影响其他脚本
#     :return:
#     """
#     return_only_price_col = kwargs.pop('return_only_price_col', True)
#     conn = kwargs.pop('conn', None)
#     replace_tj = kwargs.pop('replace_tj', False)
#     replace_price = kwargs.pop('replace_price', False)
#
#     tmp_conn = db_conn.get_derivative_data_conn() if conn is None else conn
#     c_price = f'{C_P_DAILY} as chg'
#     where = []
#
#     if return_only_price_col:
#         select = c_price
#     else:
#         select = [f'{C_DATE_DAILY} as tj', M_C, c_price]
#
#     if isinstance(manager_code, str):
#         where.append(f'{M_C}=\'{manager_code}\'')
#     elif isinstance(manager_code, list):
#         where.append(get_lst_condition(manager_code, M_C))
#     elif manager_code is None:
#         pass
#     else:
#         LOG.error('目前manager_code只支持str、list或者None，请按要求输入')
#         raise NotImplementedError
#
#     where.append(f'{C_F_T}=\'{fund_type}\'')
#
#     if start is not None:
#         where.append(f'{C_DATE_DAILY}>=\'{start}\'')
#     if end is not None:
#         where.append(f'{C_DATE_DAILY}<=\'{end}\'')
#     df = my_mysql.read_v2(
#         select=select, where=where, sfrom=T_MANAGER_DAILY_RETURN,
#         order_by=order_by, conn=tmp_conn
#     )
#     if conn is None:
#         tmp_conn.close()
#
#     if replace_tj:
#         df = df.rename(columns={'tj': 'trade_date'})
#     if replace_price:
#         df = df.rename(columns={'chg': 'daily_return'})
#
#     return df


# def get_manager_monthly_return(manager_code=None, start=None, end=None, fund_type='00', order_by=None, **kwargs):
#     """
#     得到基金经理的月收益率
#
#     :param manager_code: 基金经理代码，支持单个(str)多个(list)全部(None)
#     :param str start: 查询开始时间，格式'2020-08-01'，若只查询一天，start和end请输入同一个日期
#     :param str end: 查询结束时间，格式'2020-09-01'
#     :param str fund_type: 管理基金类型，默认'00'-全部
#     :param order_by: 按哪个字段(str)或哪几个字段(list)升序排列
#     :param kwargs:
#         - return_only_price_col: 只返回收益率字段，默认True，请不要修改默认值，以免影响其他脚本
#         - conn: 数据库连接
#         - replace_tj: 是否将tj字段名称修改为trade_date，请不要修改默认值，以免影响其他脚本
#         - replace_price: 是否将chg字段名称修改为monthly_return，请不要修改默认值，以免影响其他脚本
#     :return:
#     """
#     return_only_price_col = kwargs.pop('return_only_price_col', True)
#     conn = kwargs.pop('conn', None)
#     replace_tj = kwargs.pop('replace_tj', False)
#     replace_price = kwargs.pop('replace_price', False)
#
#     tmp_conn = db_conn.get_derivative_data_conn() if conn is None else conn
#     c_price = f'{C_P_MONTHLY} as chg'
#     where = []
#
#     if return_only_price_col:
#         select = c_price
#     else:
#         select = [f'{C_DATE_MONTHLY} as tj', M_C, c_price]
#
#     if isinstance(manager_code, str):
#         where.append(f'{M_C}=\'{manager_code}\'')
#     elif isinstance(manager_code, list):
#         where.append(get_lst_condition(manager_code, M_C))
#     elif manager_code is None:
#         pass
#     else:
#         LOG.error('目前x只支持str、list或者None，请按要求输入')
#         raise NotImplementedError
#
#     where.append(f'{C_F_T}=\'{fund_type}\'')
#
#     if start is not None:
#         where.append(f'{C_DATE_MONTHLY}>=\'{start}\'')
#     if end is not None:
#         where.append(f'{C_DATE_MONTHLY}<=\'{end}\'')
#     df = my_mysql.read_v2(
#         select=select, where=where, sfrom=T_MANAGER_MONTHLY_RETURN,
#         order_by=order_by, conn=tmp_conn
#     )
#     if conn is None:
#         tmp_conn.close()
#
#     if replace_tj:
#         df = df.rename(columns={'tj': 'trade_date'})
#     if replace_price:
#         df = df.rename(columns={'chg': 'monthly_return'})
#
#     return df


# def get_performance_data_by_indicator(indicator, asset_type, freq, asset_code, period, start=None, end=None, **kwargs):
#     """
#     读取业绩指标数据
#
#     :param str indicator: 业绩指标，如'stddev','annual_ret'等，目前暂时只支持单指标，
#                           可选'ret','risk','riskadjret','offanddef','stocktiming'这几个表中的指标
#     :param asset_code: 基金、基金经理、基金公司、指数代码。以基金为例，
#                        如果是str，返回单个基金的该指标数据
#                        如果是list，返回该基金列表中所有基金的该指标数据
#                        如果是None，返回所有基金的该指标数据
#     :param str asset_type: 类型，目前支持'fnd'-基金,'mgr'-基金经理,'comp'-基金公司,'idx'-指数
#     :param str freq: 数据频率，可选'day','month'
#     :param list period: period_interval的过滤条件，支持多区间
#     :param str start: start_date过滤条件，格式'2020-08-01'
#     :param str end: end_date过滤条件，格式'2020-09-01'
#     :param kwargs:
#         - conn: 数据库连接
#         - list fund_type: 基金经理或基金公司的独有字段，若指定，则增加fund_type的过滤条件
#     :return: pd.DataFrame，包括asset_code, start_date, end_date, period_interval, index_code, indicator_data
#     """
#     conn = kwargs.pop('conn', None)
#     fund_type = kwargs.pop('fund_type', None)
#
#     if indicator in ['ret', 'annual_ret', 'active_ret', 'annual_active_ret',
#                      'winning_rate', 'ret_persistence_score', 'ret_persistence_rank', 'ret_management']:
#         table_name_part = 'retstats'
#     elif indicator in ['stddev', 'downsidedev', 'maxdrawdown', 'skewness',
#                        'kurtosis', 'corr_coef', 'beta', 'trackerror', 'risk_management']:
#         table_name_part = 'riskstats'
#     elif indicator in ['sharperatio', 'sortinoratio', 'treynorratio', 'calmarratio', 'omegaratio',
#                        'kapparatio', 'm2', 'inforatio', 'jensen_alpha', 'annual_jensen_alpha',
#                        'riskadjret_management', 'beat_market_ability']:
#         table_name_part = 'riskadjretstats'
#     elif indicator in ['ucratio', 'dcratio', 'ucret', 'dcret', 'offense_defense']:
#         table_name_part = 'offanddef'
#     elif indicator in ['stock_ability_tm', 'timing_ability_tm', 'stock_ability_pvalue_tm', 'timing_ability_pvalue_tm',
#                        'stock_ability_rank_tm', 'timing_ability_rank_tm', 'stock_ability_hm', 'timing_ability_hm',
#                        'stock_ability_pvalue_hm', 'timing_ability_pvalue_hm', 'stock_ability_rank_hm',
#                        'timing_ability_rank_hm', 'stock_ability_cl', 'timing_ability_cl', 'stock_ability_pvalue_cl',
#                        'timing_ability_pvalue_cl', 'stock_ability_rank_cl', 'timing_ability_rank_cl',
#                        'security_choosing_ability', 'security_choosing_ability_pvalue', 'timing_ability', 'timing_ability_pvalue']:
#         table_name_part = 'stocktiming'
#     else:
#         LOG.error('输入的指标不在目前已计算的指标中，请重新输入')
#         raise NotImplementedError
#     read_table = 'mf_di_' + asset_type + table_name_part + '_' + freq
#
#     if asset_type == 'fnd':
#         c_c = 'fund_code'
#     elif asset_type == 'mgr':
#         c_c = 'manager_code'
#     elif asset_type == 'comp':
#         c_c = 'company_code'
#     elif asset_type == 'idx':
#         c_c = 'index_code'
#     else:
#         LOG.error('目前asset_type只支持fnd、mgr、comp、idx，请按要求输入')
#         raise NotImplementedError
#
#     select = [c_c, 'start_date', 'end_date', 'period_interval', indicator]
#     if (asset_type == 'fnd') or (asset_type in ['mgr', 'comp'] and table_name_part != 'stocktiming'):
#         select.append('index_code')
#     if asset_type in ['mgr', 'comp']:
#         select.append('fund_type')
#
#     tmp_conn = db_conn.get_derivative_data_conn() if conn is None else conn
#
#     where = []
#     if isinstance(asset_code, str):
#         where.append(f'{c_c}=\'{asset_code}\'')
#     elif isinstance(asset_code, list):
#         where.append(get_lst_condition(asset_code, c_c))
#     elif asset_code is None:
#         pass
#     else:
#         LOG.error('目前asset_code只支持str、list或者None，请按要求输入')
#         raise NotImplementedError
#
#     if start is not None:
#         where.append(f'{C_E_DATE}>=\'{start}\'')
#     if end is not None:
#         where.append(f'{C_E_DATE}<=\'{end}\'')
#     if period is not None:
#         where.append(get_lst_condition(period, 'period_interval'))
#     if fund_type is not None:
#         where.append(get_lst_condition(fund_type, 'fund_type'))
#
#     df = my_mysql.read_v2(
#         select=select, where=where, sfrom=read_table, conn=tmp_conn
#     )
#     if conn is None:
#         tmp_conn.close()
#
#     if not df.empty:
#         df[['start_date', 'end_date']] = df[['start_date', 'end_date']].astype(str)
#
#     return df


if __name__ == '__main__':
    # aaa = get_performance_data_by_indicator('ret', 'mgr', 'month',
    #                                         ['4579197ecf092c2a33fc6cea9da95ea6', '1fdcb8eb060057722d40512c46312fd1'],
    #                                         [5], '2020-07-01', '2020-09-01', fund_type=['00'])
    # bbb = get_fund_nav('110022', start_date='2019-12-15',
    #                    end_date='2020-12-20', only_latest=False, freq='M')
    # ccc = get_fund_return(['110022', '003095', '001625'], start_date='2019-12-01',
    #                       end_date='2020-11-30', only_latest=False, freq='M')
    # ddd = get_fund_dividend_split_info('000001')
    # eee = get_manager_nav('4579197ecf092c2a33fc6cea9da95ea6', fund_type='00', start_date='2020-01-01',
    #                       end_date='2020-11-01', only_latest=False)
    # fff = get_manager_return('4579197ecf092c2a33fc6cea9da95ea6', fund_type='00', start_date='2020-01-01',
    #                          end_date='2020-11-01', only_latest=False, freq='D')
    # ggg = get_performance_indicator_data('ret', 'index', 'D',
    #                                      ['H30045', '000300'],
    #                                      10, '2020-02-21', '2020-02-24')
    # hhh = get_company_nav('187a7a5e9e7c70b868198a6f72303c97', fund_type='00', start_date='2020-01-01',
    #                       end_date='2020-11-01', only_latest=False, freq='M')
    # iii = get_company_return('187a7a5e9e7c70b868198a6f72303c97', fund_type='00', start_date='2020-01-01',
    #                          end_date='2020-11-01', only_latest=False, freq="D")
    from quant_researcher.quant.project_tool import hammer
    jjj = get_fund_rating(['110022', '003095'], '2021-01-31', '2021-02-28', [5, 7])
    hammer.slim(jjj.tail(), a_f=1)

